package ibms_rcp.dao;

import ibms_rcp.constants.Constants;
import ibms_rcp.db.DataBaseManager;
import ibms_rcp.model.Building;
import ibms_rcp.model.Floor;
import ibms_rcp.model.Lease;
import ibms_rcp.model.Room;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class EstateManager {
	/**
	 * 得到楼列表
	 * @return
	 */
	public static List<Building> getBuildings() {
		List<Building> buildings = new ArrayList<Building>();
		// 连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			String sql = "select id, native_ems_name, native_ems_id from m2mnap_moi where moc_id=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setLong(1, Constants.MOC_BUILDING_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Building building = new Building(rs.getLong(1),
						rs.getString(2), rs.getString(3));
				buildings.add(building);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		System.out.println("BuildingManager getBuildings() called.");
		return buildings;
	}

	/**
	 * 得到楼的对应楼层列表
	 * @param buildingId
	 * @return
	 */
	public static List<Floor> getFloorsByBuilding(long buildingId) {
		List<Floor> floors = new ArrayList<Floor>();
		// 连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			String sql = "select id, native_ems_name, native_ems_id from m2mnap_moi where m2mnap_moi.id in "
					+ "(select z_moi_id from m2mnap_moi_rs where a_moi_id=? and z_moc_id=?);";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setLong(1, buildingId);
			pstmt.setLong(2, Constants.MOC_FLOOR_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Floor floor = new Floor(rs.getLong(1), rs.getString(2),
						rs.getString(3));
				floors.add(floor);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		System.out.println("FloorManager getFloorsByBuilding() called.");
		return floors;
	}

	/**
	 * 得到楼层对应的房间列表
	 * @param floorId
	 * @return
	 */
	public static List<Room> getRoomsByFloor(long floorId) {
		List<Room> rooms = new ArrayList<Room>();
		// 连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			String sql = "select id, native_ems_name, native_ems_id from m2mnap_moi where m2mnap_moi.id in "
					+ "(select z_moi_id from m2mnap_moi_rs where a_moi_id=? and z_moc_id=?);";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setLong(1, floorId);
			pstmt.setLong(2, Constants.MOC_ROOM_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Room room = new Room(rs.getLong(1), rs.getString(2),
						rs.getString(3));
				rooms.add(room);
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		System.out.println("RoomManager getRoomsByFloor() called.");
		return rooms;
	}
	
	/**
	 * 更新房间租赁信息
	 * @param room
	 */
	public static void updateRoomLeaseInfo(Room room){
		// 连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			String sql = "update m2mnap_moi_attribute set old_value=new_value, new_value=?, last_changed_time=? where moi_id=? and moc_attribute_code=?;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setLong(3, room.getId());
			pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));
			pstmt.setBoolean(1, true);//更新租赁状态为true
			pstmt.setString(4, "hireState");
			pstmt.executeUpdate();
			pstmt.setString(1, room.getBeginLeaseDate());//更新始租日期
			pstmt.setString(4, "entryDate");
			pstmt.executeUpdate();
			pstmt.setString(1, room.getEndLeaseDate());//更新到租日期
			pstmt.setString(4, "endHireDate");
			pstmt.executeUpdate();
			pstmt.setString(1, room.getBeginChargeDate());//更新开始收费日期日期
			pstmt.setString(4, "beginHireDate");
			pstmt.executeUpdate();
			boolean preCharge = room.isPreCharge();
			pstmt.setBoolean(1, preCharge);//更新是否预收费
			pstmt.setString(4, "bookFunction");
			pstmt.executeUpdate();
			if(preCharge){//如果预收费，更新物业预收费和电费预收费
				pstmt.setBoolean(1, room.isPropCharge());//更新租赁状态为true
				pstmt.setString(4, "propFunction");
				pstmt.executeUpdate();
				pstmt.setBoolean(1, preCharge);//更新是否预收费
				pstmt.setString(4, "elecFunction");
				pstmt.executeUpdate();
			}
			pstmt.close();
			//提交
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		System.out.println("EstateManager updateRoomLeaseInfo() called.");
	}
	
	//通过公司id得到公司租赁房间信息
	public static List<Lease> getLeasesByCompany(long companyId){
		List<Lease> leases = new ArrayList<Lease>();
		// 连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			String sql = "select * from view_moc_smartroom where moi_id in " +
					"(select z_moi_id from m2mnap_moi_rs where a_moi_id = ? and z_moc_id = ?);";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setLong(1, companyId);
			pstmt.setLong(2, Constants.MOC_ROOM_ID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Lease lease = new Lease();
				lease.setId(rs.getLong("moi_id"));
				lease.setRoomId(rs.getLong("id"));
				lease.setBuildingId(rs.getLong("buildingid"));
				lease.setFloorId(rs.getLong("floorid"));
				lease.setFromDate(rs.getString("entrydate"));
				lease.setToDate(rs.getString("endhiredate"));
				lease.setChargeDate(rs.getString("beginhiredate"));
				lease.setPreCharge(rs.getBoolean("bookfunction"));
				lease.setPropCharge(rs.getBoolean("propfunction"));
				lease.setElecCharge(rs.getBoolean("elecfunction"));
				leases.add(lease);
//				System.out.println(lease.toString());
			}
			rs.close();
			pstmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		System.out.println("RoomManager getRoomsByFloor() called.");
		return leases;
	}
	
	/**
	 * 清空房间租赁信息(因为房间有租赁状态属性，故只需要把租赁状态改为false，其余不需改动)
	 * @param roomId
	 */
	public static void clearRoomLeaseInfo(long roomId){
		// 连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			String sql = "update m2mnap_moi_attribute set old_value=new_value, new_value=?, last_changed_time=? where moi_id=? and moc_attribute_code=?;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setLong(3, roomId);
			pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));
			pstmt.setBoolean(1, false);//更新租赁状态为true
			pstmt.setString(4, "hireState");
			pstmt.executeUpdate();
			pstmt.close();
			//提交
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
	}
	
	/**
	 * 更新房间预收费信息
	 * @param room
	 */
	public static void updateRoomChargeInfo(Room room){
		// 连接数据库
		Connection conn = DataBaseManager.getConnection();
		try {
			//禁止自动提交
			conn.setAutoCommit(false);
			String sql = "update m2mnap_moi_attribute set old_value=new_value, new_value=?, last_changed_time=? where moi_id=? and moc_attribute_code=?;";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setLong(3, room.getId());
			pstmt.setTimestamp(2, new Timestamp(new Date().getTime()));
			boolean preCharge = room.isPreCharge();
			pstmt.setBoolean(1, preCharge);//更新是否预收费
			pstmt.setString(4, "bookFunction");
			pstmt.executeUpdate();
			if(preCharge){//如果预收费，更新物业预收费和电费预收费
				pstmt.setBoolean(1, room.isPropCharge());//更新租赁状态为true
				pstmt.setString(4, "propFunction");
				pstmt.executeUpdate();
				pstmt.setBoolean(1, preCharge);//更新是否预收费
				pstmt.setString(4, "elecFunction");
				pstmt.executeUpdate();
			}
			pstmt.close();
			//提交
			conn.commit();
		} catch (SQLException e) {
			try {
				//回滚
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		}

		System.out.println("EstateManager updateRoomLeaseInfo() called.");
	}

	public static void main(String[] args) {
//		getBuildings();
//		getFloorsByBuilding(4784);
//		getRoomsByFloor(4815);
//		Room room = new Room();
//		room.setId(4818);
//		room.setBeginLeaseDate("2011-09-28");
//		room.setEndLeaseDate("2012-09-28");
//		room.setBeginChargeDate("2011-12-01");
//		room.setPreCharge(true);
//		updateRoomLeaseInfo(room);
		getLeasesByCompany(4839);
	}
}
